package cn.java81.mychool.dao;

import cn.java81.mychool.entity.Subject;
import cn.java81.util.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author oldliu
 * @since 1.0
 */
//定义 s_subject所有的操作
public class SubjectDao {
    //CRUD
    public int add(Subject s) {
        return 1;
    }

    public int update(Subject s) {
        Connection connection = DbUtil.getConnection();
        PreparedStatement st = null;
        String sql = "update s_subject set subjectname=?,classhour=?,gradeid=? where id=?";
        try {
            st = connection.prepareStatement(sql);
            st.setString(1, s.getSubjectname());
            st.setInt(2, s.getClasshour());
            st.setLong(3, s.getGradeid());
            st.setLong(4, s.getId());
            int r = st.executeUpdate();
            return r;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            DbUtil.close(st, connection);
        }
    }

    public int del(Long id) {
        Connection connection = DbUtil.getConnection();
        PreparedStatement st = null;
        String sql = "delete s_subject   where id=?";
        try {
            st = connection.prepareStatement(sql);
            st.setLong(1, id);
            int r = st.executeUpdate();
            return r;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            DbUtil.close(st, connection);
        }
    }

    //查询
    //查询一条
    public Subject get(Long id) {
        String sql = "select id,subjectname,classhour,gradeid from s_subject where id=?";
        Connection connection = DbUtil.getConnection();
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            st = connection.prepareStatement(sql);
            st.setLong(1, id);
            rs = st.executeQuery();
            if (rs.next()) {
                return new Subject(rs.getLong("id"), rs.getString("subjectname"), rs.getInt("classhour"), rs.getLong(
                        "gradeid"));
            }
        } catch (SQLException e) {
            //System.out.println(e.getErrorCode());
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            DbUtil.close(rs, st, connection);
        }
        return null;
    }

    //分页,不返回null
    public List<Subject> search(int pageNo, int pageSize) {
        //
        String sql = "select id,subjectname,classhour,gradeid from s_subject limit ?,?";

        int start = (pageNo - 1) * pageSize;
        Connection connection = DbUtil.getConnection();
        PreparedStatement st = null;
        ResultSet rs = null;
        List<Subject> datas = new ArrayList<>(pageSize);
        try {
            st = connection.prepareStatement(sql);
            st.setLong(1, start);
            st.setInt(2, pageSize);
            rs = st.executeQuery();
            while (rs.next()) {
                datas.add(new Subject(rs.getLong("id"), rs.getString("subjectname"), rs.getInt("classhour"),
                        rs.getLong("gradeid")));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.close(rs, st, connection);
        }
        return datas;
    }
}
